<?php

class Empeval_ReportsController extends Zend_Controller_Action {

	const DATA_KPI = 'kpi';
	const DATA_TASK_GOALS = 'task / quality goals';
	
	const FILENAME_BONUS_GOALS = 'bonus_goals_report';
	const FILENAME_BONUS = 'bonus_report';
	const FILENAME_REJECTIONS = 'rejections_report';
	const FILENAME_TO_CALCULATE = 'to_calculate_report';
	
	/**
	 * 
	 * @var PHPExcel
	 */
	private $_phpExcel;
  
	public function init() {
		$this->_helper->layout->disableLayout();
		$this->_helper->viewRenderer->setNoRender(TRUE);
  	Empeval_Auth::getInstance();
  	require_once 'PHPExcel.php';
  	$this->_phpExcel = new PHPExcel();
  }
  
  public function toCalculateAction() {
  	$this->checkAccess();
  	$data = $this->executeQuery("SELECT fname, lname, email, year, period, period_from, period_to FROM view_not_calculated_forms_report");
  	$headers = array(
  			'fname',
  			'lname',
  			'email',
  			'year',
  			'period',
  			'period from',
  			'period to'
  	);
  	$this->printXlsx($headers, $data, self::FILENAME_TO_CALCULATE, range('A', 'G'));
  }
  
  public function bonusGoalsAction() {
  	$this->checkAccess();
  	$data = $this->executeQuery("SELECT type, fname, lname, department, bonus_goal_desc, desc_of_indicator, weight, realization_80, realization_100 FROM view_bonus_goals_report");
  	$headers = array(
  			'type',
  			'fname',
  			'lname',
  			'department',
  			'bonus goal description',
  			'description of indicator / criteria description',
  			'weight',
  			'realization80',
  			'realization100'
  	);
  	$this->printXlsx($headers, $data, self::FILENAME_BONUS_GOALS, range('A', 'I'));
  }
  
  public function bonusAction() {
  	$this->checkAccess();
  	$headers = array(
  			'fname',
  			'lname',
  			'department',
  			'year',
  			'period',
  			'period from',
  			'period to',
  			'% of realization total',
  			'% bonus to be paid out'
  	);
  	
  	$bonusReports = EmpevalViewBonusReportQuery::create()
  	->useKpiQuery()
  		->joinIndicators()
  	->endUse()
  	->joinTaskGoals()
  	->addJoinCondition('Kpi', 'Kpi.Status = 1')
  	->addJoinCondition('TaskGoals', 'TaskGoals.Status = 1')
  	->with('Kpi')
  	->with('Indicators')
  	->with('TaskGoals')
  	->find();
  	
  	$data = array();
  	foreach ($bonusReports as $bonusReport) {
  		$individualValue = $bonusReport->getIndividualValue();
  		$bonusValue = $bonusReport->calculateBonusValue($individualValue);
  		$realizationValue = $bonusReport->calculateRealizationValue($bonusValue, $individualValue);
  		$tmp = array();
  		$tmp[0] = $bonusReport->getFname();
  		$tmp[1] = $bonusReport->getLname();
  		$tmp[2] = $bonusReport->getDepartment();
  		$tmp[3] = $bonusReport->getYear();
  		$tmp[4] = $bonusReport->getPeriod();
  		$tmp[5] = $bonusReport->getPeriodFrom();
  		$tmp[6] = $bonusReport->getPeriodTo();
  		$tmp[7] = round($bonusValue, 2);
  		$tmp[8] = round($realizationValue, 2);
  		$data[] = $tmp;
  	}
  	$this->printXlsx($headers, $data, self::FILENAME_BONUS, range('A', 'I'));
  }
  
  public function rejectionsAction() {
  	$this->checkAccess();
  	$headers = array(
  			'fname',
  			'lname',
  			'department',
  			'year',
  			'period',
  			'period from',
  			'period to',
  			'content',
  			'decision',
  			'meeting date',
  			'committee',
  			'status'
  	);
  	 
  	$rejections = EmpevalViewRejectionsReportQuery::create()
  	->useCommitteesQuery(null, Criteria::LEFT_JOIN)
			->leftJoinUsers()
		->endUse()
		->with('Committees')
		->with('Users')
		->find();
  	 
  	$data = array();
  	foreach ($rejections as $reject) {
  		$committees = array();
  		if ($reject->getCommitteess()->count() > 0) {
	  		foreach ($reject->getCommitteess() as $committee) {
	  			$committees[] = $committee->getUsers()->getFname() . ' ' . $committee->getUsers()->getLname();
	  		}
  		}
  		$tmp = array();
  		$tmp[0] = $reject->getFname();
  		$tmp[1] = $reject->getLname();
  		$tmp[2] = $reject->getDepartment();
  		$tmp[3] = $reject->getYear();
  		$tmp[4] = $reject->getPeriod();
  		$tmp[5] = $reject->getPeriodFrom();
  		$tmp[6] = $reject->getPeriodTo();
  		$tmp[7] = $reject->getText();
  		$tmp[8] = $reject->getDecision();
  		$tmp[9] = $reject->getMeetingDate();
  		$tmp[10] = implode(', ', $committees);
  		$tmp[11] = ($reject->getStatus() == 0) ? 'open' : 'closed';
  		$data[] = $tmp;
  	}
  	$this->printXlsx($headers, $data, self::FILENAME_REJECTIONS, range('A', 'L'));
  }
  
  private function checkAccess() {
  	if (!Empeval_Auth::getInstance()->isAuth()) {
  		echo 'Access Denied!';
  		die;
  	}
  	else if (!(Empeval_Auth::getInstance()->getUser()->isAdmin() || Empeval_Auth::getInstance()->getUser()->isHr())) {
  		echo 'Access Denied!';
  		die;
  	}
  }
  
  private function setXlsxHeaders($filename) {
  	Header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  	Header('Content-Disposition: attachment;filename="' . $filename . '"');
  	Header('Cache-Control: max-age=0');
  }
  
  private function printXlsx($headers, $data, $filename, $autoSizeRange) {
  	$this->_phpExcel->getActiveSheet()->fromArray($headers, NULL, 'A1');
  	$this->_phpExcel->getActiveSheet()->fromArray($data, NULL, 'A2');
  	$this->_phpExcel->setActiveSheetIndex(0);
  	$this->_phpExcel->getActiveSheet()->setAutoFilter($this->_phpExcel->getActiveSheet()->calculateWorksheetDimension());
  	foreach ($autoSizeRange as $columnID)
  		$this->_phpExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
  	$this->setXlsxHeaders($filename);
  	$objWriter = PHPExcel_IOFactory::createWriter($this->_phpExcel, 'Excel2007');
  	$objWriter->save('php://output');
  }
  
  private function executeQuery($query) {
  	$con = Propel::getConnection('vgp_empeval');
  	$stmt = $con->prepare($query);
  	$stmt->execute();
  	return $stmt->fetchAll(PDO::FETCH_ASSOC);
  }

}